﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace PersonalAccounting.DataAccess
{
    public class DAOMoneda : IDAO<MonedaVO>
    {

        #region IDAO<MonedaVO> Members

        public bool Select(MonedaVO obj)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteParameter codigo;
            SQLiteDataReader reader;

            cmd.Connection = SQLiteConn.GetInstance().Connection;
            cmd.CommandText = QuerySelect();

            codigo = new SQLiteParameter("@codigo", obj.Codigo);

            cmd.Parameters.Add(codigo);

            reader = cmd.ExecuteReader();

            if (reader.Read())
            {
                Load(obj, reader);
                return true;
            }
            else
                return false;
        }

        public void Load(MonedaVO obj, SQLiteDataReader reader)
        {
            obj.Codigo = Convert.ToInt32(reader["MonCodigo"]);
            obj.Nombre = Convert.ToString(reader["MonNombre"]);
            obj.Simbolo = Convert.ToString(reader["MonSimbolo"]);
        }


        public bool Insert(MonedaVO obj)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteParameter codigo, nombre, simbolo;
            int records;

            cmd.Connection = SQLiteConn.GetInstance().Connection;
            cmd.CommandText = QueryInsert();

            codigo = new SQLiteParameter("@codigo", obj.Codigo);
            nombre = new SQLiteParameter("@nombre", obj.Nombre);
            simbolo = new SQLiteParameter("@simbolo", obj.Simbolo);

            cmd.Parameters.Add(codigo);
            cmd.Parameters.Add(nombre);
            cmd.Parameters.Add(simbolo);

            records = cmd.ExecuteNonQuery();

            return (records > 0);
        }
        
        public bool Update(MonedaVO obj)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteParameter codigo, nombre, simbolo;
            int records;

            cmd.Connection = SQLiteConn.GetInstance().Connection;
            cmd.CommandText = QueryUpdate();

            nombre = new SQLiteParameter("@nombre", obj.Nombre);
            simbolo = new SQLiteParameter("@simbolo", obj.Simbolo);
            codigo = new SQLiteParameter("@codigo", obj.Codigo);

            cmd.Parameters.Add(nombre);
            cmd.Parameters.Add(simbolo);
            cmd.Parameters.Add(codigo);

            records = cmd.ExecuteNonQuery();

            return (records > 0);
        }

        public bool Delete(MonedaVO obj)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteParameter codigo;
            int records;

            cmd.Connection = SQLiteConn.GetInstance().Connection;
            cmd.CommandText = QueryDelete();

            codigo = new SQLiteParameter("@codigo", obj.Codigo);

            cmd.Parameters.Add(codigo);

            records = cmd.ExecuteNonQuery();

            return (records > 0);
        }

        
        public List<MonedaVO> GetAll()
        {
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteDataReader reader = null;
            List<MonedaVO> lista = new List<MonedaVO>();
            MonedaVO moneda = null;

            cmd.Connection = SQLiteConn.GetInstance().Connection;
            cmd.CommandText = QueryGetAll();

            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                moneda = new MonedaVO();
                Load(moneda, reader);
                lista.Add(moneda);
            }

            return lista;
        }

        #endregion

        #region Public Methods

        public int GetNextCode()
        {
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteDataReader reader = null;
            int nextCode = 1;

            cmd.Connection = SQLiteConn.GetInstance().Connection;
            cmd.CommandText = QueryNextCode();

            reader = cmd.ExecuteReader();

            if (reader.Read())
                nextCode = Convert.ToInt32(reader["MonCodigo"]);

            return nextCode;
        }

        #endregion


        #region SQL

        private string QuerySelect()
        {
            return "SELECT " + Campos() +
                   "FROM Monedas " +
                   "WHERE IdMoneda = @codigo";
        }

        private string QueryGetAll()
        {
            return "SELECT " + Campos() +
                   "FROM Monedas " +
                   "ORDER BY IdMoneda";
        }

        private string QueryNextCode()
        {
            return "SELECT Max(IdMoneda) + 1 AS MonCodigo " +
                   "FROM Monedas";
        }

        public string Campos()
        {
            return " Monedas.IdMoneda AS MonCodigo," +
                   " Monedas.Nombre AS MonNombre," +
                   " Monedas.Simbolo AS MonSimbolo ";
        }

        

        private string QueryInsert()
        {
            return "INSERT INTO Monedas " +
                        "(IdMoneda, Nombre, Simbolo) " +
                   "VALUES " +
                        "(@codigo, @nombre, @simbolo)";
        }

        private string QueryUpdate()
        {
            return "UPDATE Monedas SET " +
                        "Nombre = @nombre, " +
                        "Simbolo = @simbolo " +
                   "WHERE IdMoneda = @codigo";
        }

        private string QueryDelete()
        {
            return "DELETE FROM Monedas WHERE IdMoneda = @codigo";
        }

        #endregion

    }
}